CREATE  procedure [dbo].[asi_UserTokenUpdate]
    @userKey uniqueidentifier As
declare @EveryoneRoleKey uniqueidentifier
select @EveryoneRoleKey = ParameterValue from SystemConfig
    where ParameterName = 'Security.Token.EveryoneRoleKey'

-- Create Temporary Table
CREATE TABLE #token (Grantee uniqueidentifier null, RoleName nvarchar(65))

-- Add Self
INSERT    #token(Grantee, RoleName)
VALUES    (@userKey, '')

-- Add Everyone Role
INSERT    #token(Grantee, RoleName)
VALUES    (@EveryoneRoleKey, 'Everyone')

-- Add Assigned Roles
INSERT    #token(Grantee, RoleName)
SELECT    UserRole.RoleKey, RoleMain.Name
FROM    UserRole
    INNER JOIN RoleMain ON UserRole.[RoleKey] = RoleMain.[RoleKey]
WHERE    UserRole.UserKey = @userKey
   AND    UserRole.RoleKey <> @EveryoneRoleKey

-- Add Effective Group Membership
INSERT    #token(Grantee, RoleName)
SELECT GroupKey, [Name]
FROM GroupMain
WHERE GroupKey IN (
SELECT    DISTINCT GroupKey
FROM        GroupMember
WHERE    MemberContactKey = @userKey
   AND    (JoinDate <= getdate() OR JoinDate IS NULL)
   AND    (DropDate >= getdate() OR DropDate IS NULL)
)

-- Add legacy security group - use newid because it needs to be unique and will never actually be accessed by key
INSERT    #token(Grantee, RoleName)
SELECT newid(), SecurityGroup
  FROM vBoCsWebUser a, UserMain b
WHERE a.ID = b.ContactMaster
   AND b.UserKey = @userKey
   AND LEN(LTRIM(RTRIM(SecurityGroup))) > 0

-- Add Member type - use newid because it needs to be unique and will never actually be accessed by key
INSERT    #token(Grantee, RoleName)
SELECT newid(), MemberType
  FROM vBoCsContact a, UserMain b
WHERE a.ID = b.ContactMaster
   AND b.UserKey = @userKey
   AND LEN(LTRIM(RTRIM(MemberType))) > 0

-- Add Member
DECLARE @MemberGroupKey uniqueidentifier,
@GroupName nvarchar(65)

SELECT @MemberGroupKey = ParameterValue
  FROM SystemConfig
WHERE ParameterName = 'CM.MemberGroupKey'

SELECT @GroupName = Name FROM GroupMain WHERE GroupKey = @MemberGroupKey

INSERT    #token(Grantee, RoleName)
SELECT @MemberGroupKey, @GroupName
FROM vBoCsContact a, vBoCsMemberType b, UserMain c
WHERE a.MemberType = b.MemberType
AND a.ID = c.ContactMaster
AND b.IsMember = 1
AND c.UserKey = @userKey

-- Remove Expired Records From Persisted Source
DELETE    UserToken
WHERE    UserKey = @userKey
   AND    NOT EXISTS(
        SELECT    1
         FROM    #token
         WHERE    Grantee = UserToken.Grantee)

-- Add Missing Records To Persisted Source
INSERT    UserToken(UserKey, Grantee, RoleName)
SELECT    @userKey, Grantee, RoleName
FROM    #token
WHERE    NOT EXISTS(
        SELECT    1
         FROM    UserToken
         WHERE    UserKey = @userKey
           AND    Grantee = #token.Grantee)

-- Return Tokens
SELECT    Grantee, RoleName
FROM    #token

-- Clean up
DROP TABLE #token

GO